# -*- coding: utf-8 -*-
import  pandas
import pandas as pd

url = r"E:\资料\省自然资源厅\1.example.xlsx"
#获取Excel文件第一个sheet表数据--电子监管号---实际管理区
#3601002018B00086	青云谱区
elecSuperNum2ActualManageAreaTable = pd.read_excel(rf'{url}')  # 'r'是转义字符，避免路径中的'\'被转译

#获取Excel文件第二个sheet表数据--实际管理区---设区市
#婺源县	上饶市
actualManageArea2SetCountyTable = pd.read_excel(rf'{url}', sheet_name=1)

#获取Excel文件第三个sheet表数据--实际管理区---行政区
#361130	婺源县	上饶市
setCounty2DistrictCityTable = pd.read_excel(rf'{url}', sheet_name=2)


#基数清单
baseListTable = pd.read_excel(rf'{url}', sheet_name=3)
baseListTable = baseListTable.iloc[:,4:]
#new_column_names = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W']
#baseListTable.columns = new_column_names
#baseListTable = baseListTable.iloc[2:,4:]
#baseListTable['I'] = pd.to_datetime(baseListTable['I'])
#baseListTable['I'] = baseListTable['I'].dt.strftime('%Y-%m-%d')

#baseListTable['J'] = pd.to_datetime(baseListTable['J'])
#baseListTable['J'] = baseListTable['J'].dt.strftime('%Y-%m-%d')

print('\nshuju4:\n',baseListTable)

#data444Result = pd.merge(baseListTable,elecSuperNum2ActualManageAreaTable,left_on='E',right_on='电子监管号',how='left')
#olumns_to_drop = ['电子监管号']  # 替换为实际需要去除的列名
#data444_cleaned = data444Result.drop(columns=olumns_to_drop)


column_to_move = '实际管理区'
cols = list(data444_cleaned.columns)
cols.insert(0, cols.pop(cols.index(column_to_move)))
data1_reordered = data444_cleaned[cols]

print('\ndata444Result:\n', data1_reordered)

output_file = r"E:\资料\省自然资源厅\output.xlsx"
data1_reordered.to_excel(output_file, index=False, sheet_name='Sheet1')

data5 = pd.read_excel(rf'{url}', sheet_name=4)
#print('\nshuju5:\n',data5)
data6 = pd.read_excel(rf'{url}', sheet_name=5)
#print('\nshuju6:\n',data6)